package Database;

import java.sql.*;
import java.util.ArrayList;

import Model.Appointment;
import Model.Approval;
import Model.Role;

//import Database.DBConnection;

public class AppointmentDB {
	public static int getLastNumber() {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT Appointment_ID FROM qmsdb.appointment ORDER BY Appointment_ID DESC LIMIT 1";

		int lastNumber = 0;

		try {
			stmt = con.prepareStatement(select);
			// stmt.setString(1, credID);
			rs = stmt.executeQuery();
			while (rs.next()) {
				lastNumber = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return lastNumber;
	}

	public static boolean nameExists(String name) {
		// Returns true if name already exists, returns false if name does not
		// exist yet.
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT COUNT(*) AS Number FROM Appointment WHERE Appointment_Name=?";

		int numberOfNames = -1;

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, name);
			rs = stmt.executeQuery();
			while (rs.next()) {
				numberOfNames = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		if (numberOfNames > 0)
			return true;
		else
			return false;
	}

	public static boolean idExists(String id) {
		// Returns true if name already exists, returns false if name does not
		// exist yet.
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT COUNT(*) AS Number FROM Appointment WHERE Appointment_ID=?";

		int numberOfNames = -1;

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, id);
			rs = stmt.executeQuery();
			while (rs.next()) {
				numberOfNames = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		if (numberOfNames > 0)
			return true;
		else
			return false;
	}

	public static ArrayList<Appointment> getAllAppointments() {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT * FROM Appointment ORDER BY Appointment_Name";

		ArrayList<Appointment> apptArr = new ArrayList<Appointment>();
		Appointment appt = null;

		try {
			stmt = con.prepareStatement(select);
			// stmt.setString(1, credID);
			rs = stmt.executeQuery();
			while (rs.next()) {
				appt = new Appointment();
				appt.setId(rs.getString("Appointment_ID"));
				appt.setName(rs.getString("Appointment_Name"));
				appt.setResponsibilities(rs
						.getString("Appointment_Responsibilities"));
				appt.setDateUpdated(rs.getString("Appointment_DateUpdated"));

				apptArr.add(appt);
			}
		} catch (Exception e) {
			e.printStackTrace();
			appt = null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return apptArr;
	}

	public static Appointment getAppointmentbyID(String id) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT * FROM Appointment WHERE Appointment_ID = ?";

		Appointment appt = null;

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, id);
			rs = stmt.executeQuery();
			if (rs.next()) {
				appt = new Appointment();
				appt.setId(rs.getString("Appointment_ID"));
				appt.setName(rs.getString("Appointment_Name"));
				appt.setResponsibilities(rs
						.getString("Appointment_Responsibilities"));
				appt.setDateUpdated(rs.getString("Appointment_DateUpdated"));
			}
		} catch (Exception e) {
			e.printStackTrace();
			appt = null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return appt;
	}

	public static void createAppointment(Appointment appt) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		String select = "INSERT INTO Appointment(Appointment_ID, Appointment_Name, Appointment_Responsibilities) VALUES(?, ?, ?)";

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, appt.getId());
			stmt.setString(2, appt.getName());
			stmt.setString(3, appt.getResponsibilities());

			stmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
	}

	public static void updateAppointment(Appointment appt) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		String select = "UPDATE Appointment SET Appointment_Name = ?, Appointment_Responsibilities = ? WHERE Appointment_ID = ?";

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, appt.getName());
			stmt.setString(2, appt.getResponsibilities());
			stmt.setString(3, appt.getId());

			stmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
	}

	public static void deleteAppointment(String id) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		String select = "DELETE FROM Appointment WHERE Appointment_ID = ?";

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, id);

			stmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
	}

	public static ArrayList<Approval> getAllAppointments(String roleType) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT staffappointment.UA_EffectiveDate, staff.staff_name, appointment.appointment_name, appointmentgroup.apmg_description, ua_md5, staffappointment.UA_AppointmentID, staffappointment.UA_CreatedBy, staffappointment.UA_Class, staffappointment.UA_Diploma, staffappointment.UA_Year, staffappointment.UA_YearNo FROM staffappointment INNER JOIN appointment ON appointment.appointment_ID = staffappointment.UA_AppointmentID INNER JOIN staff ON staff.staff_loginid = staffappointment.UA_LoginID INNER JOIN appointmentgroup ON appointmentgroup.apmg_id = staffappointment.ua_apptgrpid WHERE staffappointment.ua_status < 1";

		ArrayList<Approval> apptArr = new ArrayList<Approval>();
		Approval appt = null;

		try {
			stmt = con.prepareStatement(select);
			// stmt.setString(1, roleType);
			rs = stmt.executeQuery();
			while (rs.next()) {
				appt = new Approval();
				appt.setName(rs.getString("staff_name"));
				appt.setAppointment(rs.getString("appointment_name"));
				appt.setDescription(rs.getString("apmg_description"));
				appt.setMd5(rs.getString("ua_md5"));
				appt.setDate(rs.getString("UA_EffectiveDate"));
				appt.setAppointmentID(rs.getString("UA_AppointmentID"));
				appt.setCreatedBy(rs.getString("UA_CreatedBy"));
				appt.setSaclass(rs.getString("UA_Class"));
				appt.setDiploma(rs.getString("UA_Diploma"));
				appt.setYear(rs.getString("UA_Year"));
				appt.setYearNo(rs.getString("UA_YearNo"));

				apptArr.add(appt);
			}
		} catch (Exception e) {
			e.printStackTrace();
			appt = null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return apptArr;
	}


}
